package com.deductive.mapper;


import com.alibaba.fastjson2.JSONObject;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author 王浩
 * @createDate 2025-2-17 10:23:42
 */
@Repository
public interface PgDBMapper {



    int updateDynamic(Map<String, Object> parameters);

    @Update("${updateSql}")
    void dynamicUpdate(String updateSql);

    /**
     * 查询表名
     *
     * @return
     */
    @Select("select tablename from pg_tables where schemaname = 'public';")
    List<JSONObject> getTableName();


    /**
     * 查询字段名
     *
     * @return
     */
//    @Select("select column_name,data_type,udt_name from information_schema.columns where table_schema = 'public' and table_name = #{tableName}")
//    @Select("select column_name from information_schema.columns where table_schema = 'public' and table_name = #{tableName}")
    @Select("select column_name,udt_name,character_maximum_length from information_schema.columns where table_schema = 'public' and table_name = #{tableName}")
    List<JSONObject> getColumns(String tableName);


    /**
     * 查询表字段名和别名
     *
     * @return
     */
    @Select("SELECT \n" +
            "    c.column_name,\n" +
            "    c.data_type,\n" +
            "    c.character_maximum_length,\n" +
            "    c.is_nullable,\n" +
            "    c.column_default,\n" +
            "    pgd.description as column_comment\n" +
            "FROM \n" +
            "    information_schema.columns c\n" +
            "LEFT JOIN \n" +
            "    pg_catalog.pg_statio_all_tables st \n" +
            "    ON c.table_schema = st.schemaname \n" +
            "    AND c.table_name = st.relname\n" +
            "LEFT JOIN \n" +
            "    pg_catalog.pg_description pgd \n" +
            "    ON pgd.objoid = st.relid \n" +
            "    AND pgd.objsubid = c.ordinal_position\n" +
            "WHERE \n" +
            "    c.table_schema = 'public'  \n" +
            "    AND c.table_name = #{tanleName}\n" +
            "ORDER BY \n" +
            "    c.ordinal_position;")
    List<JSONObject> getColumnsContainsComment(String tableName);


    @Select("select tablename from pg_tables where schemaname = 'public' and tablename = #{tableName}")
    JSONObject checkExistingTables(String tableName);


//    /**
//     * 查询字段名和类型
//     *
//     * @return
//     */
//    @Select("select column_name from information_schema.columns where table_schema = 'public' and table_name = #{tableName}")
//    List<JSONObject> getColumns(String tableName);

    /**
     * 修改表字段
     */
    @Update("alter table \"${tableName}\" rename column \"${columnName}\" to \"${newColumnName}\"")
    void updateColumn(@Param("tableName") String tableName, @Param("columnName") String columnName, @Param("newColumnName") String newColumnName);


    /**
     * 删除数据表
     *
     * @param tableName
     */
    @Delete("drop table if exists \"${tableName}\"")
    void deleteTable(String tableName);


    /**
     * 根据表名删除字段名
     *
     * @param tableName
     * @param columnName
     */
    @Delete("ALTER TABLE \"${tableName}\"\n" +
            "DROP COLUMN \"${columnName}\";")
    void deleteColumnByTable(@Param("tableName") String tableName, @Param("columnName") String columnName);


    /**
     * 动态拼接查询
     *
     * @param dataDynamic
     * @return
     */
    @Select("${dataDynamic}")
    List<JSONObject> selectDataDynamic(String dataDynamic);


    @Select("SELECT id, 名称,ST_AsText(geom)\n" +
            "FROM #{tableName}\n" +
            "WHERE ST_Contains(geom, ST_GeomFromText('${point}', 4090)) limit 1 ;")
   JSONObject selectContains(@Param("tableName") String tableName, @Param("point") String point);

    @Delete("delete from ${tableName} where id = #{id}")
    void deleteOne(@Param("tableName")String tableName,@Param("id")Long id);

    @Insert("${sql}")
    void insertOne(String sql);

    @Insert("${sql}")
    void insertMeter(String sql);

    @Update("${sql}")
    void updateOne(String sql);


    /**
     * 动态拼接统计
     *
     * @param dataDynamic
     * @return
     */
    @Select("${dataDynamic}")
    JSONObject selectDataDynamicCount(String dataDynamic);

    @Select("${dataDynamic}")
    JSONObject selectDataDynamicOne(String dataDynamic);

    @Select("select 名称 ,null as 市县名,st_asgeojson(geom) as geom from 市_县级行政区")
    List<JSONObject> selectXzqhSX();


    @Select("select 名称 ,市县名  from 乡_镇级行政区 where 市县名 = #{parentName}")
    List<JSONObject> selectXzqhXZ(String parentName);




}
